﻿using Dapper;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using static 人员管理.PersonUtils;

namespace 人员管理
{
    public class PersonUtils
    {
        private static readonly string constr = "Data Source=.;Initial Catalog=test;User Id=sa;Password=root123;";

        internal IEnumerable<Person> SelectAll()
        {
            using (var connection = new SqlConnection(constr))
            {
                var persons = connection.Query<Person>("SELECT id,name,age,sex,addr FROM Person order by id asc ");
                return persons;
            }
        }

        internal bool EditPerson(long selId, string name, string age, string sex, string addr)
        {
            bool b = false;

            Person person = new Person() 
            { 
                ID = selId,
                Name = name,
                Addr = addr,
                Age = Convert.ToInt32(age), 
                Sex = sex
            
            };

            using (var connection = new SqlConnection(constr))
            {
                int v = connection.Execute("UPDATE Person SET name = @name,age = @age,sex = @sex,addr = @addr WHERE Id = @Id", person);
                b = v > 0;
            }
            return b;
        }

        internal bool DeletePerson(long selId)
        {
            bool b = false;

            using (var connection = new SqlConnection(constr))
            {
                int v = connection.Execute("DELETE FROM Person WHERE Id = @Id", new {Id = selId});
                b = v > 0;
            }
            return b;
        }

        internal bool InsertPerson(string name, string age, string sex, string addr)
        {
            bool b = false;

            Person person = new Person()
            {
                Name = name,
                Addr = addr,
                Age = Convert.ToInt32(age),
                Sex = sex

            };

            using (var connection = new SqlConnection(constr))
            {
                int v = connection.Execute("INSERT INTO Person (name,age,sex,addr) VALUES (@name, @age,@sex,@addr)", person);
                b = v > 0;
            }
            return b;

        }
    }
}
